package dao

import (
	"database/sql"
	"fmt"
	"log"
	"my-web-app/user/entity"

	_ "github.com/go-sql-driver/mysql"
)

func CreateUserMySql() {
	db, err := sql.Open("mysql", "root:algebrafly@tcp(127.0.0.1:3306)/TestDB?charset=utf8mb4")
	db.Ping()
	defer db.Close()

	if err != nil {
		fmt.Println("connect DB error !")
		log.Fatalln(err)
	}

	_, err2 := db.Query("INSERT INTO book VALUES(4, 'xxxxxxx')")
	if err2 != nil {
		log.Fatal(err2)
	}

	fmt.Println("successfully insert item")
}

func DeleteItem() {
	db, err := sql.Open("mysql", "root:algebrafly@tcp(127.0.0.1:3306)/TestDB?charset=utf8mb4")
	db.Ping()
	defer db.Close()

	if err != nil {
		fmt.Println("connect DB error !")
		log.Fatalln(err)
	}

	sql := "DELETE FROM user WHERE id = 1"
	res, err2 := db.Exec(sql)

	if err2 != nil {
		panic(err2.Error())
	}

	affectedRows, err := res.RowsAffected()

	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("delete item success, statement affected %d rows\n", affectedRows)
}

func QueryItem() []entity.User {
	db, err := sql.Open("mysql", "root:algebrafly@tcp(192.168.6.63:3306)/test?charset=utf8mb4")
	db.Ping()
	defer db.Close()

	if err != nil {
		fmt.Println("connect DB error !")
		log.Fatalln(err)
	}

	// var mid int = 1

	result, err2 := db.Query("SELECT * FROM user limit 10")
	if err2 != nil {
		log.Fatal(err2)
	}

	books := []entity.User{}
	for result.Next() {

		var id int
		var name string

		err = result.Scan(&id, &name)

		if err != nil {
			log.Fatal(err)
		}

		fmt.Printf("id: %d, name: %s\n", id, name)
		var b entity.User
		b.Name = name
		books = append(books, b)
	}
	return books
}
